<!DOCTYPE html>
<html>
<head>
    <title></title>
    <link rel="stylesheet" type="text/css" href="../公用CSS文件/all.css" />
    <script type="text/javascript" src="../js/jquery.min.js"></script> 	<script type="text/javascript" src="../js/js.js"></script>
    <meta charset="utf-8">
    <style type="text/css">
        table{min-width:80%}
    </style>
</head>
<body>
<div class="dt">mysql相关概念</div>
<ul>
    <table>
        <tr>
            <th>概念</th><th>说明</th>
        </tr>
        <tr>
            <td>mysql</td>
            <td>mysql是一种开放源代码的关系型数据库管理系统（RDBMS），使用最常用的数据库管理语言--结构化查询语言（SQL）进行数据库管理</td>
        </tr>
        <tr>
            <td>sql</td>
            <td>结构化查询语言(Structured Query Language)简称SQL，是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统。sql由IBM研发，后来被国际标准化组织ISO采纳为关系型数据库操作语言的国际标准</td>
        </tr>
        <tr>
            <td>database(库)</td>
            <td>在mysql中，database存储数据库对象的容器，数据库对象包含数据表table、视图view等</td>
        </tr>
        <tr>
            <td>table(表)</td>
            <td>mysql存储数据的基本数据库对象，包含字段和值，用来存储数据库最核心的构成--数据</td>
        </tr>
    </table>
</ul>

<div class="dt">mysql存储引擎</div>
<ul>
    <div class="ltable showimg">mysql存储引擎概念</div>
    <div class="img">
        <li>为了适应不同的应用场景，mysql以插件的形式集成了不同的存储机制。不同的存储机制对应着数据的存储方式、查询等的不同，这就是存储引擎engine</li>
    </div>

    <div class="ltable showimg">mysql三大存储引擎</div>
    <div class="img">
        <table>
            <tr>
                <th>特性</th><th>Myisam</th><th>InnoDB</th><th>Memory</th>
            </tr>
            <tr>
                <td>存储大小</td>
                <td>无限制(最大由操作系统允许的最大值决定)</td>
                <td>64TB</td>
                <td>无限制(最大由操作系统允许的最大值决定)</td>
            </tr>
            <tr>
                <td>索引支持性</td>
                <td>支持B树索引、全文索引</td>
                <td>支持B树索引、集群索引</td>
                <td>支持B树索引、哈希索引</td>
            </tr>
            <tr>
                <td>锁机制</td>
                <td>表锁</td>
                <td>行锁</td>
                <td>表锁</td>
            </tr>
            <tr>
                <td>事务和外键支持性</td>
                <td>不支持</td>
                <td>支持</td>
                <td>不支持</td>
            </tr>
            <tr>
                <td>内存占用</td>
                <td>低(仅缓存索引)</td>
                <td>高(缓存索引和查询结果)</td>
                <td>高(所有数据聚集存储在内存，结构存储在硬盘)</td>
            </tr>
            <tr>
                <td>空间使用</td>
                <td>低(无额外结构)</td>
                <td>高</td>
                <td>低(硬盘仅存储含表结构)</td>
            </tr>
            <tr>
                <td>写入效率</td>
                <td>高</td>
                <td>低(因为索引和数据分开存储及事务开销)</td>
                <td>高</td>
            </tr>
            <tr>
                <td>数据缓存</td>
                <td>仅缓存索引不缓存数据</td>
                <td>缓存索引和数据(内存)</td>
                <td>索引和数据都在内存中</td>
            </tr>
        </table>
        <li>mysql普通索引的性质就是b树索引</li>
    </div>

    <div class="ltable showimg">存储引擎相关操作和命令</div>
    <div class="img">
        <table>
            <tr>
                <th>操作</th><th>命令</th>
            </tr>
            <tr>
                <td>查看引擎</td>
                <td>show engines</td>
            </tr>
            <tr>
                <td>查看引擎相关设置</td>
                <td>show variables like "%engine%"</td>
            </tr>
        </table>
        <li>查看引擎设置时，default_storage_engine为默认存储引擎，可以修改my.ini的配置修改默认存储引擎</li>
    </div>
</ul>

<div class="dt">mysql数据类型</div>
<ul>
    <div class="ltable showimg">整型</div>
    <div class="img">
        <table>
            <tr>
                <th>数据类型</th><th>占用空间</th><th>长度</th>
            </tr>
            <tr>
                <td>tinyint</td>
                <td>1字节</td>
                <td>最高3位</td>
            </tr>
            <tr>
                <td>smallint</td>
                <td>2字节</td>
                <td>最高5位</td>
            </tr>
            <tr>
                <td>mediumint</td>
                <td>3字节</td>
                <td>最高8位(有符号7)，默认7位</td>
            </tr>
            <tr>
                <td>int</td>
                <td>4字节</td>
                <td>最高10位，无论有无符号默认10位</td>
            </tr>
            <tr>
                <td>bigint</td>
                <td>8字节</td>
                <td>最高20位(有符号19)，默认19位</td>
            </tr>
        </table>
        <li>整形字段能存储2^字节个数字，有符号正数和负数各占一半，有符号的最大值为pow(2，字节数*8 - 1)-1，无符号的最大值为pow(2，字节数*8)-1，依据最大值可以推算最大长度</li>
    </div>

    <div class="ltable showimg">浮点定点型</div>
    <div class="img">
        <table>
            <tr>
                <th>类型</th><th>占用字节</th><th>说明</th>
            </tr>
            <tr>
                <td>float</td>
                <td>4个字节</td>
                <td>有效精度7位，最大长度255</td>
            </tr>
            <tr>
                <td>double</td>
                <td>8个字节</td>
                <td>有效精度8位，最大长度255</td>
            </tr>
            <tr>
                <td>decimal</td>
                <td>字段长度个字节</td>
                <td>最大长度65位(精度同)</td>
            </tr>
        </table>
        <li>单精度浮点数存储范围-3.40E+38 ~ +3.40E+38，双精度浮点数存储范围-1.79E+308 ~ +1.79E+308</li>
        <li>decimal占用字节多主要是因为虽然decimal存储的是数字，但却是以字符串形式存储的</li>
        <li>浮点数进行聚合运算如sum时也会按精度进行四舍五入</li>
    </div>

    <div class="ltable showimg">时间日期型</div>
    <div class="img">
        <table>
            <tr>
                <th>数据类型</th><th>占用字节</th><th>最小值</th><th>最大值</th>
            </tr>
            <tr>
                <td>date</td>
                <td>4个字节</td>
                <td>1000-01-01</td>
                <td>9999-12-31</td>
            </tr>
            <tr>
                <td>datetime</td>
                <td>8个字节</td>
                <td>1000-01-01 00:00:00</td>
                <td>9999-12-31 23:59:59</td>
            </tr>
            <tr>
                <td>timestamp</td>
                <td>4个字节</td>
                <td>19700101080001</td>
                <td>2038年的某个时刻</td>
            </tr>
            <tr>
                <td>time</td>
                <td>3个字节</td>
                <td>-835:59:59</td>
                <td>838:59:59</td>
            </tr>
            <tr>
                <td>year</td>
                <td>1个字节</td>
                <td>1901</td>
                <td>2155</td>
            </tr>
        </table>
    </div>

    <div class="ltable showimg">字符串和text</div>
    <div class="img">
        <table>
            <tr>
                <th>数据类型</th><th>占用字节</th><th>说明</th>
            </tr>
            <tr>
                <td>char</td>
                <td>字符长度个字节</td>
                <td>char为定长字符串，未足最大长度会右侧填充空格</td>
            </tr>
            <tr>
                <td>varchar</td>
                <td>字符长度个字节(最多字段长度个字节，5.0版本前为255后为65535)</td>
                <td>变长字符串</td>
            </tr>
            <tr>
                <td>tinytext</td>
                <td>1+字符长度(1个字节存储长度)</td>
                <td>tinytext最大存储255个字符，占用256个字节</td>
            </tr>
            <tr>
                <td>text</td>
                <td>2+字符长度</td>
                <td>text最大存储pow(2，16)-1个字符</td>
            </tr>
            <tr>
                <td>mediumtext</td>
                <td>3+字符长度</td>
                <td>mediumtext最大存储pow(2，24)-1个字符</td>
            </tr>
            <tr>
                <td>longtext</td>
                <td>4+字符长度</td>
                <td>longtext最大存储pow(2，32)-1个字符</td>
            </tr>
        </table>
        <li>varchar和char类型的字段可以设置长度，text类型的无法设置长度</li>
        <li>在查询插入性能上char优于varchar，varchar优于text</li>
    </div>

    <div class="ltable showimg">其他类型(二进制)</div>
    <div class="img">
        <table>
            <tr>
                <th>类型</th><th>字节</th><th>说明</th>
            </tr>
            <tr>
                <td>bit</td>
                <td>1~8字节</td>
                <td>二进制数字，试图存入10进制数字则会转化为2进制</td>
            </tr>
            <tr>
                <td>binary</td>
                <td>最大255</td>
                <td>定长二进制数据，类似char最大长度也是255</td>
            </tr>
            <tr>
                <td>varbinary</td>
                <td>最大65532</td>
                <td>变长二进制数据，最大长度65532(5.7版本实测)</td>
            </tr>
            <tr>
                <td>blob</td>
                <td>二进制数据，类似text</td>
            </tr>
        </table>
        <li>blob也和text一样，拥有派生的tinytext、mediumtext、longtext等类型</li>
    </div>

    <div class="ltable showimg">字节、编码和存储字符长度</div>
    <div class="img">
        <table>
            <tr>
                <th>编码</th><th>存储字符/字节</th>
            </tr>
            <tr>
                <td>UTF-8</td>
                <td>编码字符占用3个字节，英文字母数字等占用1个</td>
            </tr>
            <tr>
                <td>GBK</td>
                <td>编码字符占用2个字节，英文字母数字占用1个</td>
            </tr>
        </table>
    </div>
</ul>

<div class="dt">mysql字段约束</div>
<ul>
    <div class="ltable showimg">字段约束类型</div>
    <div class="img">
        <table>
            <tr>
                <th>类型</th><th>使用</th><th>说明</th>
            </tr>
            <tr>
                <td>非空约束</td>
                <td>指明数据类型后追加not null</td>
                <td>非空约束的字段值不能为null，一般结合default使用</td>
            </tr>
            <tr>
                <td>唯一约束</td>
                <td>指明数据类型后追加unique</td>
                <td>唯一约束会在修改和插入数据时检查列中是否存在该值(null可以有多个)</td>
            </tr>
            <tr>
                <td>主键约束</td>
                <td>指明数据类型后追加primary key</td>
                <td>主键约束依据关系型数据库的实体完整性准则自带非空约束、唯一约束的属性</td>
            </tr>
            <tr>
                <td>外键约束</td>
                <td>声明所有字段后追加constraint 外键名 foreign key(字段名) references 表名(字段名)</td>
                <td>外键约束后，外键的值只能引用自另外一个表的指定字段的值</td>
            </tr>
            <tr>
                <td>默认值</td>
                <td>指明数据类型后追加default 值</td>
                <td>设置默认值后在插入数据时字段未指定值时为默认值，同时设置非空约束值时为null也为默认值</td>
            </tr>
            <tr>
                <td>自增</td>
                <td>指明数据类型int后追加auto_increment</td>
                <td>设置自增后如果未指定值或为null则会在表的AUTO_INCREMENT的基础上自增1，自增列必须被索引且一个表仅有一个自增列</td>
            </tr>
            <tr>
                <td>无符号</td>
                <td>指明数值型数据类型后追加unsigned</td>
                <td>设置无符号后不能插入负数数值</td>
            </tr>
        </table>
        <li>所有约束和字段属性都无排列顺序，可以随意组合</li>
        <li>唯一约束、主键约束、外键约束实际上都会在列上添加基础的btree索引，唯一约束和主键约束也都能约束多个列</li>
        <li>唯一约束和主键约束除了在声明字段时声明，也可以在声明字段之后声明，格式为"约束类型" (字段...)，在这样操作时unique和unique key、unique index是等价的</li>
        <li>外键约束本身是一种数据完整性约束，仅在两个表都为InnoDb时才会建立成功，也出于完整性约束，外键引用的值发生改动时需要同步操作外键的值，声明外键时也能声明发生引用值改动时的操作</li>
    </div>

    <div class="ltable showimg">外键约束同步操作</div>
    <div class="img">
        <table>
            <tr>
                <th>操作</th><th>on delete</th><th>on update</th>
            </tr>
            <tr>
                <td>No action(默认)、Restrict</td>
                <td>父表删除记录时，不允许删除有对应外键的记录</td>
                <td>父表更新记录时，不允许删除有对应外键的记录</td>
            </tr>
            <tr>
                <td>Cascade(级联)</td>
                <td>父表删除记录时，同时删除对应外键的记录</td>
                <td>父表更新记录时，同时更新对应外键的记录</td>
            </tr>
            <tr>
                <td>Set null</td>
                <td>父表删除记录时，将对应外键的值设为null(需要允许为null)</td>
                <td>父表更新记录时，将对应外键的值设为Null</td>
            </tr>
        </table>
        <li>No action和Restrict的区别是Restrict在更新之前判断阻止，而No action在更新之后检查，如果新的外键有对应的父表的记录则不进行阻止</li>
    </div>

    <div class="ltable showimg">外键的使用</div>
    <div class="img">
        <li>外键适用于数据一致性要求高、业务逻辑比较复杂，团队、模块复杂的场景，数据量大或更改插入频繁则不适用外键</li>
        <li>外键主要影响的是写入性能，子表在进行insert、update、delete等操作时，父表会被加上共享锁，父表删除和更新数据会执行事务检查</li>
        <li>外键仅在父表和子表都为InnoDB时才会建立成功，被引用的父表字段必须有唯一约束或主键约束</li>
    </div>
</ul>

<div class="dt">mysql索引</div>
<ul>
    <div class="ltable showimg">索引的概念</div>
    <div class="img">
        <li>索引是数据库中一种特殊的数据结构，包含对原数据值的表示和对原数据的引用指针，类似于图书的目录，通过目录可以快速定位需要查找的数据，加快查询效率</li>
        <li>在mysql使用中，经常作为查询条件(where、having)的字段和参与分组的字段适合建立索引，尽管有些资料甚至书籍说外键也适合，但外键是默认添加索引的</li>
    </div>

    <div class="ltable showimg">mysql主要索引类型</div>
    <div class="img">
        <table>
            <tr>
                <th>索引</th><th>特性</th><th>说明</th>
            </tr>
            <tr>
                <td>聚集索引(clustered tables)</td>
                <td>不同表的数据行存储在一个数据块作为索引</td>
                <td>mysql无法自定义cluster索引，仅InnoDB的主键以聚簇索引存储</td>
            </tr>
            <tr>
                <td>B树索引(B+tree tables)</td>
                <td>B树结构(层次模型)的索引，b+tree仅在叶子节点存储数据</td>
                <td>mysql默认的索引类型就是B+tree</td>
            </tr>
            <tr>
                <td>哈希索引(Hash tables)</td>
                <td>用Hash算法为要索引的列的值生成一个hash值，通过哈希值列数据索引源数据</td>
                <td>mysql三大存储引擎仅memory支持hash索引</td>
            </tr>
            <tr>
                <td>全文索引(Text index)</td>
                <td>为要索引的文章内容数据按词条(term)统计并记录其出现的位置和频率并定位至源数据</td>
                <td>mysql仅myisam支持全文索引</td>
            </tr>
            <tr>
                <td>空间索引</td>
                <td>空间索引是指依据空间对象的位置和形状或空间对象之间的某种空间关系按一定的顺序排列的一种数据结构</td>
                <td>常见空间索引类型有BSP树、K－D－B树、R树、R+树和CELL树</td>
            </tr>
        </table>
        <li>聚集、聚簇、集群索引都是指的cluster索引，该索引适用于为关联性比较强的数据结构做索引以便于快速定位，所以InnoDb的主键使用cluster索引</li>
        <li>全文索引的基本原理是先定义一个词库，然后在文章中查找每个词条(term)出现的频率和位置，把这样的频率和位置信息按照词库的顺序归纳，这样就相当于对文件建立了一个以词库为目录的索引，查找某个词的时候就能很快的定位到该词出现的位置</li>
        <li>mysql的全文索引性能比较差，一般使用Sphinx、Solr，其中Solr性能更优良、Sphinx与mysql更容易组合使用，mysql会自动为每一个词(最小粒度可配置)创建词库term，并将50%的数据出现的term视为无效词(可配置) ，查询时条件为match(字段) against('关键词')</li>
    </div>

    <div class="ltable showimg">Hash索引与B+tree索引的选择</div>
    <div class="img">
        <li>Hash索引仅仅在等值和非等值运算时才会被使用，部分索引查询也不会被使用</li>
        <li>Hash索引索引值的排序和原值的排序不一定相关，所以不适合进行查询结果排序</li>
        <li>Hash索引检索效率非常高，索引的检索可以一次定位，不像B-Tree索引需要从根节点到枝节点，最后才能访问到页节点这样多次的IO访问，但Hash算法无法100%保证无碰撞所以仍然要对比表数据(Hash值相同数据不一定相同)</li>
    </div>

    <div class="ltable showimg">Hash算法</div>
    <div class="img">
        <li>Hash算法是一类加密算法，可以为任意长度的数据映射为一个新值，称该值为原值的Hash值或散列值</li>
        <li>Hash算法的特性是可压缩、不可逆、无碰撞(实际上无法绝对保证)，满足这些特性的算法都可以被称为Hash算法</li>
    </div>

    <div class="ltable showimg">mysql索引相关操作</div>
    <div class="img">
        <table>
            <tr>
                <th>操作</th><th>说明</th>
            </tr>
            <tr>
                <td>创建表时创建索引</td>
                <td>index|key 【索引名=字段】 （字段名） 【asc|desc】</td>
            </tr>
            <tr>
                <td>create语句创建索引</td>
                <td>create index 索引名 on 表名(属性名) 【asc|desc </td>
            </tr>
            <tr>
                <td>删除索引</td>
                <td>drop index 索引名 on 表名</td>
            </tr>
            <tr>
                <td>查看索引</td>
                <td>show index from 表名</td>
            </tr>
        </table>
        <li>在建表后添加索引除了使用create语句，也可以使用alter语句，使用alter table tablename add 创建表指定索引语句</li>
        <li>索引可以为多列、唯一等，唯一索引的本质在B+tree索引的基础上添加唯一约束</li>
        <li>mysql进行查询时可以强制使用或不使用索引，强制使用语法为：select 字段 from 表 force index(索引名)，也可以使用use index这样的语句，强制不使用为ignore，强制使用的意义其实在于指定使用而其他的索引将不会考虑</li>
    </div>
</ul>

<div class="dt">mysql用户管理</div>
<ul>
    <div class="ltable showimg">user表主要字段</div>
    <div class="img">
        <table>
            <tr>
                <th>字段</th><th>意义</th>
            </tr>
            <tr>
                <td>host</td>
                <td>地址，与user同为联合主键</td>
            </tr>
            <tr>
                <td>user</td>
                <td>用户名，与host同为联合主键</td>
            </tr>
            <tr>
                <td>password</td>
                <td>密码，值为密文</td>
            </tr>
            <tr>
                <td>max_questions</td>
                <td>每小时允许执行的查询数</td>
            </tr>
            <tr>
                <td>max_connections</td>
                <td>每小时允许的连接次数</td>
            </tr>
            <tr>
                <td>max_user_connections</td>
                <td>单个用户同时能拥有的连接个数</td>
            </tr>
        </table>
        <li>mysql的权限机制除了user表，还包含db、host、tables_priv、columns_priv、procs_priv(存储过程和函数)等</li>
    </div>

    <div class="ltable showimg">mysql用户管理命令</div>
    <div class="img">
        <table>
            <tr>
                <th>操作</th><th>说明</th>
            </tr>
            <tr>
                <td>create创建用户</td>
                <td>create user '用户名'@'host' identified by '密码'</td>
            </tr>
            <tr>
                <td>grant授予权限</td>
                <td>grant 权限 on 库.表 to '用户名'@'host' 【,'用户名'@'host'...】</td>
            </tr>
            <tr>
                <td>grant创建用户并赋予权限</td>
                <td>grant 权限 on 库.表 to '用户名'@'host' identified by '密码' 【,'用户名'@'host' identified by '密码'...】</td>
            </tr>
            <tr>
                <td>invoke删除权限</td>
                <td>grant 权限 on 库.表 to '用户名'@'host' 【,'用户名'@'host'...】</td>
            </tr>
            <tr>
                <td>修改用户密码</td>
                <td>set password 【for '用户名'@'密码'】=password('密码')</td>
            </tr>
            <tr>
                <td>查看用户权限</td>
                <td>show grants for '用户名'@'host'</td>
            </tr>
        </table>
        <li>任意用户权限发生改变后，需要执行flush privileges刷新权限</li>
        <li>权限类型一般为关键字对应权限如alter、drop、update、create等，所有权限为all privileges，所有表库为*.*</li>
        <li>host字段有ip地址、localhost、%三种表示，localhost表示本地，%表示任意ip</li>
        <li>用户账户和密码都是保存在数据库中的，所以也可以直接操作数据库记录进行用户管理</li>
    </div>
</ul>

<div class="dt">mysql安全和权限管理</div>
<ul>
    <div class="ltable showimg">sql防注入常用方法</div>
    <div class="img">
        <table>
            <tr>
                <th>方法</th><th>操作</th><th>说明</th><th>缺陷</th>
            </tr>
            <tr>
                <td>预处理语句和参数化查询</td>
                <td>使用PDO、mysqli的参数绑定</td>
                <td>参数会被处理并被mysql识别为字符串</td>
                <td>mysql5.1之后提供的参数绑定功能，之前版本无此功能，无其他缺陷</td>
            </tr>
            <tr>
                <td>参数过滤</td>
                <td>使用addslashes、mysql_real_escape_string函数</td>
                <td>将参数整体识别为字符串(通过转义引号、NULL和的方式)</td>
                <td>addslashes可能造成宽字节注入，mysql_real_escape_string(考虑mysql字符集)在PHP7.0被废弃</td>
            </tr>
            <tr>
                <td>开启magic_quotes_gpc配置</td>
                <td>将php.ini的magic_quotes_gpc配置项设置为on</td>
                <td>执行sql时自动进行魔法引用(使用pdo、mysqli时，一般框架也是用它)</td>
                <td>PHP6和之后的版本不支持开启</td>
            </tr>
        </table>
    </div>

</ul>



</body>
</html>